﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;
using System.Data;

namespace TB.DALSqlProvider
{
    public class DianpuDALSqlProvider : IDAL.IDALDianpu
    {	
        public int Insert(TB.Entity.dianpu obj)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetStoredProcCommand("Proc_dianpu_InsertUpdate") as SqlCommand;
            
            cmd.Parameters.AddWithValue("@shopId", obj.shopId == 0 ? DBNull.Value : (object)obj.shopId);
            cmd.Parameters.AddWithValue("@shopTitle", obj.shopTitle == String.Empty ? DBNull.Value : (object)obj.shopTitle);
            cmd.Parameters.AddWithValue("@nickname", obj.nickname == String.Empty ? DBNull.Value : (object)obj.nickname);
            cmd.Parameters.AddWithValue("@userId", obj.userId == String.Empty ? DBNull.Value : (object)obj.userId);
            
            cmd.Parameters.AddWithValue("@dpVer", obj.dpVer == String.Empty ? DBNull.Value : (object)obj.dpVer);
            cmd.Parameters.AddWithValue("@sjName", obj.sjName == String.Empty ? DBNull.Value : (object)obj.sjName);
            cmd.Parameters.AddWithValue("@templateID", obj.templateID ==String.Empty ? DBNull.Value : (object)obj.templateID);
            cmd.Parameters.AddWithValue("@dpXy", obj.dpXy == String.Empty ? DBNull.Value : (object)obj.dpXy);
            
            cmd.Parameters.AddWithValue("@dpURL", obj.dpURL == String.Empty ? DBNull.Value : (object)obj.dpURL);
            cmd.Parameters.AddWithValue("@kefu", obj.kefu == String.Empty ? DBNull.Value : (object)obj.kefu);
            cmd.Parameters.AddWithValue("@hanye", obj.hanye == String.Empty ? DBNull.Value : (object)obj.hanye);
            cmd.Parameters.AddWithValue("@location", obj.location == String.Empty ? DBNull.Value : (object)obj.location);
            
            cmd.Parameters.AddWithValue("@createTime", obj.createTime == String.Empty ? DBNull.Value : (object)obj.createTime);
            cmd.Parameters.AddWithValue("@dgTime", obj.dgTime == String.Empty ? DBNull.Value : (object)obj.dgTime);
            cmd.Parameters.AddWithValue("@cjTime", obj.cjTime == String.Empty ? DBNull.Value : (object)obj.cjTime);

            cmd.Parameters.AddWithValue("@cjNum", 0);
            cmd.Parameters.AddWithValue("@reverse2", obj.reverse2 == String.Empty ? DBNull.Value : (object)obj.reverse2);
            cmd.Parameters.AddWithValue("@reverse3", obj.reverse3 == String.Empty ? DBNull.Value : (object)obj.reverse3);

            return db.ExecuteNonQuery(cmd);
            //return 0;
        }
        public int GetDianpuSum()
        {
            string strSql = "select count(*) from dianpu";
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            int sum = Convert.ToInt32(db.ExecuteScalar(cmd));
            return sum;
        }
        public int Update(TB.Entity.dianpu obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("Proc_dianpu_InsertUpdate") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@shopId", obj.shopId == 0 ? DBNull.Value : (object)obj.shopId);
            cmd.Parameters.AddWithValue("@shopTitle", obj.shopTitle == String.Empty ? DBNull.Value : (object)obj.shopTitle);
            cmd.Parameters.AddWithValue("@nickname", obj.nickname == String.Empty ? DBNull.Value : (object)obj.nickname);
            cmd.Parameters.AddWithValue("@userId", obj.userId == String.Empty ? DBNull.Value : (object)obj.userId);
            cmd.Parameters.AddWithValue("@dpVer", obj.dpVer == String.Empty ? DBNull.Value : (object)obj.dpVer);
            cmd.Parameters.AddWithValue("@sjName", obj.sjName == String.Empty ? DBNull.Value : (object)obj.sjName);
            cmd.Parameters.AddWithValue("@templateID", obj.templateID == String.Empty ? DBNull.Value : (object)obj.templateID);
            cmd.Parameters.AddWithValue("@dpXy", obj.dpXy == String.Empty ? DBNull.Value : (object)obj.dpXy);
            cmd.Parameters.AddWithValue("@dpURL", obj.dpURL == String.Empty ? DBNull.Value : (object)obj.dpURL);
            cmd.Parameters.AddWithValue("@kefu", obj.kefu == String.Empty ? DBNull.Value : (object)obj.kefu);
            cmd.Parameters.AddWithValue("@hanye", obj.hanye == String.Empty ? DBNull.Value : (object)obj.hanye);
            cmd.Parameters.AddWithValue("@location", obj.location == String.Empty ? DBNull.Value : (object)obj.location);
            cmd.Parameters.AddWithValue("@createTime", obj.createTime == String.Empty ? DBNull.Value : (object)obj.createTime);
            cmd.Parameters.AddWithValue("@dgTime", obj.dgTime == String.Empty ? DBNull.Value : (object)obj.dgTime);
            cmd.Parameters.AddWithValue("@cjTime", obj.cjTime == String.Empty? DBNull.Value : (object)obj.cjTime);
            //cmd.Parameters.AddWithValue("@reverse1", obj.cjNum == String.Empty ? DBNull.Value : (object)obj.reverse1);
            cmd.Parameters.AddWithValue("@reverse2", obj.reverse2 == String.Empty ? DBNull.Value : (object)obj.reverse2);
            cmd.Parameters.AddWithValue("@reverse3", obj.reverse3 == String.Empty ? DBNull.Value : (object)obj.reverse3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        public List<TB.Entity.dianpu> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_dianpu_loadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.dianpu> list = new List<TB.Entity.dianpu>();
            while (reader.Read())
            {
                list.Add(new TB.Entity.dianpu()
                {
                    shopId = reader["shopId"] == DBNull.Value ? 0 : (int)reader["shopId"],
                    shopTitle = reader["shopTitle"] == DBNull.Value ? String.Empty : (string)reader["shopTitle"],
                    nickname = reader["nickname"] == DBNull.Value ? String.Empty : (string)reader["nickname"],
                    userId = reader["userId"] == DBNull.Value ? String.Empty : (string)reader["userId"],
                    dpVer = reader["dpVer"] == DBNull.Value ? String.Empty : (string)reader["dpVer"],
                    sjName = reader["sjName"] == DBNull.Value ? String.Empty : (string)reader["sjName"],
                    templateID = reader["templateID"] == DBNull.Value ? String.Empty : (string)reader["templateID"],
                    dpXy = reader["dpXy"] == DBNull.Value ? String.Empty : (string)reader["dpXy"],
                    dpURL = reader["dpURL"] == DBNull.Value ? String.Empty : (string)reader["dpURL"],
                    kefu = reader["kefu"] == DBNull.Value ? String.Empty : (string)reader["kefu"],
                    hanye = reader["hanye"] == DBNull.Value ? String.Empty : (string)reader["hanye"],
                    location = reader["location"] == DBNull.Value ? String.Empty : (string)reader["location"],
                    createTime = reader["createTime"] == DBNull.Value ? String.Empty :reader["createTime"].ToString(),
                    dgTime = reader["dgTime"] == DBNull.Value ? String.Empty : (string)reader["dgTime"],
                    cjTime = reader["cjTime"] == DBNull.Value ? String.Empty : (string)reader["cjTime"],
                    cjNum = (int)reader["cjNum"],
                    reverse2 = reader["reverse2"] == DBNull.Value ? String.Empty : (string)reader["reverse2"],
                    reverse3 = reader["reverse3"] == DBNull.Value ? String.Empty : (string)reader["reverse3"],
                });
            }
            return list;
        }
        /// <summary>
        /// 得到前10000未更新的店铺,且返回未更新数据总数
        /// </summary>
        /// <param name="noGxSum">返回未更新的记录数</param>
        /// <param name="zdDate">指定日期</param>
        /// <param name="daysNum">指定日期前的天数</param>
        /// <returns></returns>
        public DataSet LoadAllGx(out int noGxSum,string zdDate, int daysNum)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_dianpu_loadGx") as SqlCommand;
            cmd.Parameters.AddWithValue("@daysNum",daysNum);
            cmd.Parameters.AddWithValue("@zdDate", zdDate);
            cmd.Parameters.Add("@noGxSum", SqlDbType.Int).Direction = ParameterDirection.Output;
            db.ExecuteNonQuery(cmd);
            noGxSum = (int)cmd.Parameters["@noGxSum"].Value;
            //3.通过db执行一个DBReader
            DataSet ds = db.ExecuteDataSet(cmd);
            return ds;
        }
        public System.Data.DataSet PartPageFunc(int pageCurrent, int pageSize, out int counts, out int pageNums, string conditionWhere)
        {
            List<Entity.dianpu> list = new List<Entity.dianpu>();
            Database db = DatabaseFactory.CreateDatabase();

            #region  此处代码要注意思，对于存储过程要同时返回结果集和返回参数，要同时用ExecuteReader和ExecuteNonQuery
            /*var cmd = db.GetStoredProcCommand("outparamTest") as SqlCommand;
            //cmd.Parameters.AddWithValue("@a", "admin");
            SqlParameter para = new SqlParameter("@c", SqlDbType.Int, 4);
            para.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para);

            SqlParameter para1 = new SqlParameter("@b", SqlDbType.Int, 4);
            para1.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(para1);

            //cmd.Parameters.Add("@c", SqlDbType.Int).Direction = ParameterDirection.Output;
            //cmd.Parameters.Add("@b", SqlDbType.Int).Direction = ParameterDirection.Output;
            var reader=db.ExecuteReader(cmd);
            int x=db.ExecuteNonQuery(cmd);
            while (reader.Read())
           {
                NVR.Entity.DeviceInfo devInfo = new NVR.Entity.DeviceInfo();
                list.Add(devInfo);
            }*/
            #endregion
            var cmd = db.GetStoredProcCommand("proc_paged_part_select") as SqlCommand;
            cmd.Parameters.AddWithValue("@tblName", "dianpu"); //表名
            cmd.Parameters.AddWithValue("@fldName", "shopId,dpURL,nickname,userId,dpXy,createTime,location,hanye,dpVer,sjName,templateID,cjTime"); //要返回的字段名
            cmd.Parameters.AddWithValue("@pageSize", pageSize);
            cmd.Parameters.AddWithValue("@page", pageCurrent);
            cmd.Parameters.AddWithValue("@fldSort", "shopId"); //要排序的字段
            cmd.Parameters.AddWithValue("@Sort", 1);  //0 升序，1：降序
            cmd.Parameters.AddWithValue("@strCondition", conditionWhere);
            cmd.Parameters.AddWithValue("@ID", "shopId"); //此处的参数与fldName要有相关性。
            cmd.Parameters.AddWithValue("@Dist", 0);
            cmd.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@Counts", SqlDbType.Int).Direction = ParameterDirection.Output;
            db.ExecuteNonQuery(cmd);
            counts = (int)cmd.Parameters["@Counts"].Value;
            pageNums = (int)cmd.Parameters["@pageCount"].Value;
            return db.ExecuteDataSet(cmd);
        }
        public int Delete(TB.Entity.dianpu obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_dianpu_delete") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@shopId", obj.shopId);
            cmd.Parameters.AddWithValue("@nickname", obj.nickname);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        public TB.Entity.dianpu LoadById(int shopId,string nickname)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_dianpu_loadById") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@shopId", shopId);
            cmd.Parameters.AddWithValue("@nickname", nickname);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.dianpu entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.dianpu()
                {
                    shopId=reader["shopId"]==DBNull.Value?0:(int)reader["shopId"],
                    shopTitle=reader["shopTitle"]==DBNull.Value?String.Empty:(string)reader["shopTitle"],
                    nickname=reader["nickname"]==DBNull.Value?String.Empty:(string)reader["nickname"],
                    userId=reader["userId"]==DBNull.Value?String.Empty:(string)reader["userId"],
                    dpVer=reader["dpVer"]==DBNull.Value?String.Empty:(string)reader["dpVer"],
                    sjName=reader["sjName"]==DBNull.Value?String.Empty:(string)reader["sjName"],
                    templateID=reader["templateID"]==DBNull.Value?String.Empty:(string)reader["templateID"],
                    dpXy=reader["dpXy"]==DBNull.Value?String.Empty:(string)reader["dpXy"],
                    dpURL=reader["dpURL"]==DBNull.Value?String.Empty:(string)reader["dpURL"],
                    kefu=reader["kefu"]==DBNull.Value?String.Empty:(string)reader["kefu"],
                    hanye=reader["hanye"]==DBNull.Value?String.Empty:(string)reader["hanye"],
                    location=reader["location"]==DBNull.Value?String.Empty:(string)reader["location"],
                    createTime=reader["createTime"]==DBNull.Value?String.Empty:(string)reader["createTime"],
                    dgTime=reader["dgTime"]==DBNull.Value?String.Empty:(string)reader["dgTime"],
                    cjTime=reader["cjTime"]==DBNull.Value?String.Empty:(string)reader["cjTime"],
                    cjNum =  (int)reader["cjNum"],
                    reverse2=reader["reverse2"]==DBNull.Value?String.Empty:(string)reader["reverse2"],
                    reverse3=reader["reverse3"]==DBNull.Value?String.Empty:(string)reader["reverse3"],
                };
            }
            return entity;
        }
        public System.Data.DataSet GetDataSet(string strSql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //string strSql = "select top 5000 d.dpURL from dianpu d where d.sjName is not NULL and d.templateID is NULL";
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            System.Data.DataSet ds=db.ExecuteDataSet(cmd);
            return ds;
        }
        public int UpdateTemplateId(int shopID, string templateId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string strSql = "update dianpu SET templateID = '" + templateId + "' where shopId=" + shopID;
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            return db.ExecuteNonQuery(cmd);
        }
    }
}
